Remove a PostgreSQL Database Using Psycopg and Python

Overview:

A database in PostgreSQL is a container for schemas that contain several namespaces and tables underneath.
PostgreSQL databases map to the catalogs as defined by the SQL standards.
Database creation and removal operations are not frequent operations when compared to insert, update and deletion of data.
The removal operation of a database involves removal of the underlying schemas, namespaces and tables. Hence care must be taken to ensure necessary backups have been made prior to removal of a database.

Example:

# ----- Example Python program that removes a PostgreSQL database -----
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

# Connect to PostgreSQL DBMS
postgresConnection = psycopg2.connect("user=test password=test");
postgresConnection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);

# Obtain a database Cursor
cur_ob = postgresConnection.cursor();
name_db = "MyTestDatabase";

# Create database statement
sqlCreateDB = "create database "+name_db+";"

# Create a database in PostgreSQL database
cur_ob.execute(sqlCreateDB);

# SQL statement for querying the list of databases
sqlQuery = "SELECT datname FROM pg_database WHERE datistemplate = false;";

# Execute the query statement
cur_ob.execute(sqlQuery);

# Retrieve all the rows from the cursor
rows = cur_ob.fetchall();

print("---List of databases before removing one:---");
for row in rows:
print('%s' %(row[0]));

# Remove database statement
sqlRemDb = "DROP database "+name_db+";"

cur_ob.execute(sqlRemDb);

# Remove the PostgreSQL database
cur_ob.execute(sqlQuery);

# Retrieve all the rows from the cursor
rows = cur_ob.fetchall();

print("---List of databases after removing one:---");

for row in rows:
print('%s' %(row[0]));

Output:

---List of databases before removing one:---

postgres

selvamvinod

test

socialmedia

mytestdatabase

---List of databases after removing one:---

postgres

selvamvinod

test

socialmedia